﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.Common;
using System.Xml.Linq;
using System.Data;
using System.IO;

namespace DbSharp.Framework.Storage.IO
{
    class ExcelFileWriterHelper
    {

        /// <summary>
        /// For old excel file, make sure the outputFileName extension cannot be xlsx, or the outputfile is readonly. 
        /// http://blog.benday.com/archive/2007/04/06/22772.aspx
        /// </summary>
        /// <param name="outputFileName"></param>
        private static string GetExcel2003ConnectionString(string outputFileName)
        { 
            string connectionStringFmt = @"Provider=Microsoft.Jet.OLEDB.4.0; 
                Data Source={0}; 
                Extended Properties=""Excel 8.0;HDR=No""";
            return string.Format(connectionStringFmt, outputFileName);
        }


        private static string GetExcel2007ConnectionString(string outputFileName)
        { 
            string connectionStringFmt = @"Provider=Microsoft.ACE.OLEDB.12.0; 
                Data Source={0}; 
                Extended Properties=""Excel 12.0 Xml;HDR=No""";
            return  string.Format(connectionStringFmt, outputFileName);
        }


        private static string GetExcel2003TableCreationSQL()
        {
            // CREATE TABLE Sheet1 (F1 char(1), F2 char(1)) 
            string sqlFmt = "CREATE TABLE Sheet1 ({0})";
            StringBuilder sb = new StringBuilder();
            for (int i = 1; i < 257; i++)
            {
                sb.Append("F");
                sb.Append(i);
                //Max data size is 255 char
                sb.Append(" char(255),");
            }
            sb.Length = sb.Length - 1;
            string sql = string.Format(sqlFmt, sb.ToString());
            return sql;
        }


        private static string GetExcel2007TableCreationSQL()
        {
            // CREATE TABLE Sheet1 (F1 char(1), F2 char(1)) 
            string sqlFmt = "CREATE TABLE Sheet1 ({0})";
            StringBuilder sb = new StringBuilder();
            for (int i = 1; i < 257; i++)
            {
                sb.Append("F");
                sb.Append(i);
                //Max data size is 255 char
                sb.Append(" char(255),");
            }
            sb.Length = sb.Length - 1;
            string sql = string.Format(sqlFmt, sb.ToString());
            return sql;
        }



        public static string GetConnectionString(string excelFileName, bool is2007Excel)
        {
            string connectionString;
            if (is2007Excel == false)
                connectionString = GetExcel2003ConnectionString(excelFileName);
            else
                connectionString = GetExcel2007ConnectionString(excelFileName);
            return connectionString;
        }


        public static string GetTableCreationSQL(bool is2007Excel)
        {
            string sql;
            if (is2007Excel == false)
                sql = GetExcel2003TableCreationSQL();
            else
                sql = GetExcel2007TableCreationSQL();
            return sql;
        }


        public static string GetSheetDefaultTitle()
        {
            return "Notice: Please remove this row manually before the Excel file will be used for future data comparison. ";
        }
    }


    class ExcelFileWriter
    {
        #region Private members

        private string m_ConnectionString; 
        private bool m_Is2007Excel;

        #endregion

        #region Private methods

        private void CreateExcelFile(OleDbCommand cmd, string sheetTitle)
        {
            string sql;
            sql = ExcelFileWriterHelper.GetTableCreationSQL(m_Is2007Excel);
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();

            //UPDATE [Sheet1$] SET F2 = 'XYZ' WHERE F1 = '111'
            //UPDATE [Sheet1$A1:A1] SET F1='TestValue1';
            string sqlFmt = "UPDATE [Sheet1$A1:A1] Set F1='{0}'";
            sheetTitle = sheetTitle.Replace("'", "''");

            cmd.CommandText = string.Format(sqlFmt, sheetTitle);
            cmd.ExecuteNonQuery();
        }

        private string BuildInsertRowSql(string rowsGroupName, IEnumerable<string> listColumnValues)
        {
            string sqlFmt = "INSERT INTO [Sheet1$] ({0}) values ({1})";
            string sql = string.Empty;
            StringBuilder sb1 = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();
            int index = 1;

            //in Sheet1, the first column is group name if group name is assigned
            if (string.IsNullOrEmpty(rowsGroupName) == false)
            {
                sb1.Append('F');
                sb1.Append(index);
                sb1.Append(",");

                sb2.Append("'");
                sb2.Append(rowsGroupName.Replace("'", "''"));
                sb2.Append("',");
                index++;
            }

            foreach (var column in listColumnValues)
            {
                sb1.Append('F');
                sb1.Append(index);
                sb1.Append(",");

                sb2.Append("'");
                sb2.Append(column.Replace("'", "''"));
                sb2.Append("',");
                index++;
            }

            if (index > 1)
            {
                //trim the last Comma symbol
                sb1.Length = sb1.Length - 1;
                sb2.Length = sb2.Length - 1;
                sql = string.Format(sqlFmt, sb1.ToString(), sb2.ToString());
            }
            return sql;
        }

        private void InsertRow(string insertSql, OleDbCommand cmd)
        {
            if (string.IsNullOrEmpty(insertSql) == false)
            {
                cmd.CommandText = insertSql;
                cmd.ExecuteNonQuery();
            }
        }



        #endregion


        /// <summary>
        /// For old excel file, make sure the outputFileName extension cannot be xlsx, or the outputfile is readonly. 
        /// http://blog.benday.com/archive/2007/04/06/22772.aspx
        /// </summary>
        /// <param name="outputFileName"></param>
        public ExcelFileWriter(string outputFileName, bool is2007Excel)
        { 
            m_Is2007Excel = is2007Excel;
            m_ConnectionString = ExcelFileWriterHelper.GetConnectionString(outputFileName, is2007Excel);
        }



        #region Public property/methods

        public List<string> GetFieldColumnHeaders(IEnumerable<XElement> notNullRecordRows)
        {
            List<string> listHeader = null;
            foreach (var row in notNullRecordRows)
            {
                var q = from field in row.Elements()
                        select field.Name.ToString();

                listHeader = q.ToList<string>();
                break;
            }
            if (listHeader == null)
            {
                listHeader = new List<string>();
                listHeader.Add(string.Empty);
            }
            return listHeader;
        }

        public void SaveToFile(string sheetTitle, string groupColumnHeader, List<string> fieldColumnHeaders, List<string> listRowsGroupName, List<IEnumerable<XElement>> listRowsGroup, List<long> listExportRecordCount)
        {
            string rowsGroupName;
            IEnumerable<XElement> ListRecordRow;
            long exportRecordCount = 0;
            string sql;

            using (OleDbConnection conn = new OleDbConnection(m_ConnectionString))
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;

                //Create Excel file
                CreateExcelFile(cmd, sheetTitle);

                //if fieldColumnHeaders is null, it indicates there is no records at all.
                if (fieldColumnHeaders == null)
                    return;

                //insert field header
                rowsGroupName = groupColumnHeader;
                sql = BuildInsertRowSql(rowsGroupName, fieldColumnHeaders);
                InsertRow(sql, cmd);

                //enumerate all rows group
                for (int i = 0; i < listRowsGroupName.Count; i++)
                {
                    rowsGroupName = listRowsGroupName[i];
                    ListRecordRow = listRowsGroup[i];
                    exportRecordCount = listExportRecordCount[i];

                    long exportRecordLimited = long.MaxValue;
                    if (exportRecordCount >= 0)
                        exportRecordLimited = exportRecordCount;

                    long index = 0;
                    if (ListRecordRow == null)
                        continue;
                    //insert field value
                    foreach (var row in ListRecordRow)
                    {
                        if (index >= exportRecordLimited)
                            break;
                        index++;

                        var q = from field in row.Elements()
                                select field.Value;
                        sql = BuildInsertRowSql(rowsGroupName, q);
                        InsertRow(sql, cmd);
                    }
                }
            }
        }
    

        public void SaveToFile(XElement dataRootElement)
        {
            string sheetTitle = "";

            using (OleDbConnection conn = new OleDbConnection(m_ConnectionString))
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                string sql;

                //Create Excel file
                CreateExcelFile(cmd, sheetTitle);

                var list = new List<string>();

                //insert field title
                list.Clear();
                list.Add("scalarFieldName");
                string rowsGroupName = string.Empty;
                sql = BuildInsertRowSql(rowsGroupName, list);
                InsertRow(sql, cmd);

                //insert field value
                list.Clear();
                list.Add(dataRootElement.Value);
                sql = BuildInsertRowSql(rowsGroupName, list);
                InsertRow(sql, cmd);
            }
        }

  
        #endregion
    }
}
